 IF EXISTS (SELECT * 
	   FROM   sysobjects 
	   WHERE  name = N'spm_GetUpMileStoneCode')
	DROP FUNCTION spm_GetUpMileStoneCode
GO

CREATE FUNCTION spm_GetUpMileStoneCode
(
@Code varchar(256)
)
RETURNS varchar(256)
AS
BEGIN
declare @rcode varchar(256)
set @rcode=reverse(@code)

declare @p int
set @p=charindex('(',@rcode,1)

if @p<>0
begin
	set @code=substring(@code,1,len(@code)-@p)
	return @code
end

return ''

END

GO 
print dbo.spm_GetUpMileStoneCode('(1)(2)(3)')
print dbo.spm_GetUpMileStoneCode('(1)(2)(3)(4)')
print dbo.spm_GetUpMileStoneCode('(1)')

